﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.ComponentModel;
namespace Applib.SqlUtilities
{
    public static class SqlDataUtility
    {
        public static string ConnectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=TSQL2012;Integrated Security=True";

        public static int ExecuteNonQuery(string command, params SqlParameter[] parameters)
        {
            return ExecuteNonQuery(command, false, parameters);
        }
        public static int ExecuteNonQuery(string command, bool isStoredProcedure, params SqlParameter[] parameters)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand(command, connection))
                {
                    if (isStoredProcedure)
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                    }

                    connection.Open();
                    if (parameters != null)
                    {
                        foreach (SqlParameter par in parameters)
                        {
                            cmd.Parameters.Add(par);
                        }
                    }
                    return cmd.ExecuteNonQuery();
                }
            }
        }



        public static object ExecuteScalar(string command, bool isStoredProcedure, params SqlParameter[] parameters)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand(command, connection))
                {
                    if (isStoredProcedure)
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                    }

                    connection.Open();
                    if (parameters != null)
                    {
                        foreach (SqlParameter par in parameters)
                        {
                            cmd.Parameters.Add(par);
                        }
                    }

                    object obj = cmd.ExecuteScalar();
                    if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                    {
                        return 0;
                    }
                    else
                    {
                        return obj;
                    }
                }
            }
        }

        public static object ExecuteScalar(string command, params SqlParameter[] parameters)
        {
            return ExecuteScalar(command, false, parameters);
        }

        public static int ExecuteTransaction(List<string> commandList)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    int count = 0;
                    for (int n = 0; n < commandList.Count; n++)
                    {
                        string strsql = commandList[n];
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            count += cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                    return count;
                }
                catch
                {
                    tx.Rollback();
                    throw;
                }
            }
        }
        
        public static SqlDataReader ExecuteReader(string command)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(command, connection);

                connection.Open();
                SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return myReader;

            }
        }

        public static void Query(string command, IListSource source)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                
                connection.Open();
                SqlDataAdapter adapter = new SqlDataAdapter(command, connection);
                if (source is DataTable)
                {
                    adapter.Fill(source as DataTable);
                }
                else if (source is DataSet)
                {
                    adapter.Fill(source as DataSet);
                }
                else
                {
                    throw new ArgumentException("type is not supported.", "source");
                }
            }
        }

        internal static bool HandleSqlException(SqlException sqlEx)
        {
            switch (sqlEx.Number)
            {
                case -2:            // seen after unplugging the net cord on the primary
                case 53:            // seen when trying to connect during failover
                case 10054:         // seen when trying to connect during failover
                case 18456:         // seen when log in failed for user.
                case 2:             // seen when SQL Server is stoppped.
                case 4060:          // seen when database doesn't exists
                    return true;
                default:
                    return false;
            }
        }

        public static int DataAccessWithRetry(Action dataAccess, Action returnAction)
        {
            throw new NotImplementedException();
        }
    }
}
